from common.recordlog import logs
from conf.operationConfig import OperationConfig
import pymysql

conf = OperationConfig()


class ConnectMysql():
    """连接读取mysql数据库的数据"""

    def __init__(self):

        mysql_conf = {
            "host": conf.get_mysql_conf("host"),
            "port": int(conf.get_mysql_conf("port")),
            "user": conf.get_mysql_conf("user"),
            "password": conf.get_mysql_conf("password"),
            "database": conf.get_mysql_conf("database")
        }

        try:
            self.conn = pymysql.connect(**mysql_conf, charset='utf8')
            # 创建游标
            # cursor=pymysql.cursors.DictCursor:将数据库表字段显示，以key:value形式显示
            self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
            logs.info("""成功连接到数据库
            host：{host}
            port：{port}
            db：{database}
            """.format(**mysql_conf))
        except Exception as e:
            logs.error(e)

    def close(self):
        if self.conn and self.cursor:
            self.cursor.close()
            self.conn.close()
        return True

    def query(self, sql, params=None):
        """查询数据"""
        try:
            if params:
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            res = self.cursor.fetchall()
            logs.info(f"SQL: {sql},查询成功")
            return res
        except Exception as e:
            logs.error(f"SQL: {sql}, Params: {params}, Error: {e}")
        finally:
            self.close()

    def insert(self, sql, params=None, commit=True):
        """新增数据"""
        try:
            if params:
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            if commit:
                self.conn.commit()
            logs.info(f"SQL: {sql},新增数据成功")
            return True
        except Exception as e:
            logs.error(f"SQL: {sql}, Params: {params}, Error: {e}")
            return False
        finally:
            self.close()

    def update(self, sql, params=None, commit=True):
        """修改数据"""
        try:
            if params:
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            if commit:
                self.conn.commit()
            logs.info(f"SQL: {sql},修改成功")
            return True
        except Exception as e:
            logs.error(f"SQL: {sql}, Params: {params}, Error: {e}")
            return False
        finally:
            self.close()

    def delete(self, sql, params=None, commit=True):
        """删除数据"""
        try:
            if params:
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            if commit:
                self.conn.commit()
            logs.info(f"SQL: {sql},删除成功")
            return True
        except Exception as e:
            logs.error(f"SQL: {sql}, Params: {params}, Error: {e}")
            return False
        finally:
            self.close()


if __name__ == '__main__':
    conn = ConnectMysql()
    sql = 'SELECT * FROM yonghu'
    # sql = 'UPDATE yonghu SET mima=1234567 WHERE xingming="李五"'
    print(conn.query(sql))
